﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_BDDB_ByCompanyId]
	(
	
	@userid VARCHAR(10),
	@StartDate NCHAR(10),
	@EndDate NCHAR(10),
	@CompanyId VARCHAR(500),
	@mid int	
	
	)
AS
BEGIN
	--销售额等于百度开户+百度服务费-退款销售额  续费销售额等于续费+百度服务费续费-续费退款销售额  毛利是一样的 
 --DECLARE @xzcds INT ,@sxs INT,@zsxs int,@xdxse DECIMAL(12,2),@xdml DECIMAL(12,2),@xfxse  DECIMAL(12,2),@xfml DECIMAL(12,2),@xse DECIMAL(12,2),@ml DECIMAL(12,2) 
	DECLARE @strSql VARCHAR(max)--,@strSql1 VARCHAR(max)
	SET @strSql='
	;WITH list as (
SELECT * FROM bddb b WHERE startdate ='''+@StartDate+''' AND enddate='''+@EndDate+''' AND companyid IN ('+@CompanyId+')

  and  CHARINDEX(''[''+CONVERT(VARCHAR, CompanyId)+'']'' , 
                       (SELECT au.Cid FROM aspnet_Users au where au.username ='''+@UserId+'''),0)>0
and mid = '+ CONVERT(VARCHAR, @mid)+')

select * from list ,(select sum(xzcds) as xzcds1,sum(sxs) as sxs1,sum(zsxs) as zsxs1,sum(xdxse) as xdxse1,sum(xdml) as xdml1
, sum(xfxse) as xfxse1 ,sum(xfml) as xfml1,sum(xse) as xse1,sum(ml) as ml1 from list ) as tb order by companyid 

'
EXEC(@strSql)
print(@strSql)
END
